﻿<cfsilent>

	<cfquery name="queryTeacher" datasource="U_IMP_SYNC">
		SELECT 
			tid, tchname, idcard, gender, depid, tpcode, educode, tchtype
		FROM 
			imp_teacher
	</cfquery>

	<cfset syncOpt = event.getArg("SynOpt", "new") />

	<cfset dataAdded = 0 />
	<cfset dataUpdated = 0 />

	<cfset datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice") />

	<cfset currentTS = datetimeAdvice.getCurrentDatetimeString() />
    
    <cfset rs_teacherType = queryNew("tch_type")/>
    <cfset rs_teacherTechnicalPosition = queryNew("tp_id")/>
    <cfset rs_teacherDepartment = queryNew("institute_id")/>
    <cfset rs_teacherEduBackground = queryNew("edu_id")/>

</cfsilent>

<p class="notice">获取 <cfoutput>#queryTeacher.recordCount#</cfoutput> 条教师档案数据记录</p>

<cfswitch expression="#syncOpt#">
	<cfcase value="new">
		
		<cftry>
			<cftransaction>
				<cfloop query="queryTeacher">
					<!--- 循环中心数据库记录, 尝试追加新纪录 --->
					
                    <cfset sql = "SELECT a.tch_id 
									FROM t_teacher a 
									WHERE a.tch_id = :tchId "/>
                         
                    <cfset queryObj = new Query(datasource = application.dnsMaster)/>
					<cfset queryObj.addParam(name="tchId", value=queryTeacher.tid, cfsqltype="cf_sql_varchar")/>
                    
                    <cfset rs_teacher = queryObj.execute(sql=sql).getResult()/>
					
					<!--- 字典组件缓存 --->
					<cfif rs_teacherType.recordCount GT 0 and rs_teacherType.tch_type eq queryTeacher.tchtype>
						<cfelse>
						
                            <cfset sql = "SELECT a.tch_type 
											FROM t_teacher_type a 
											WHERE a.tch_type = :tchId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="tchId", value=queryTeacher.tchtype, cfsqltype="cf_sql_char")/>
                            
                            <cfset rs_teacherType = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					<cfif rs_teacherTechnicalPosition.recordCount GT 0 and rs_teacherTechnicalPosition.tp_id eq queryTeacher.tpcode>
						<cfelse>
						
                            <cfset sql = "SELECT a.tp_id  
											FROM t_technical_position a 
											WHERE a.tp_id = :tchId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="tchId", value=queryTeacher.tpcode, cfsqltype="cf_sql_char")/>
                            
                            <cfset rs_teacherTechnicalPosition = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					<cfif rs_teacherDepartment.recordCount GT 0 and rs_teacherDepartment.institute_id eq queryTeacher.depid>
						<cfelse>
							
                            <cfset sql = "SELECT a.institute_id 
											FROM t_institute a 
											WHERE a.institute_id = :insId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="insId", value=queryTeacher.depid, cfsqltype="cf_sql_varchar")/>
                            
                            <cfset rs_teacherDepartment = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					<cfif rs_teacherEduBackground.recordCount GT 0 and rs_teacherEduBackground.edu_id eq queryTeacher.educode>
						<cfelse>
							
                            <cfset sql = "SELECT a.edu_id 
											FROM t_teacher_educational a 
											WHERE a.edu_id = :tchId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="tchId", value=queryTeacher.educode, cfsqltype="cf_sql_char")/>
                            
                            <cfset rs_teacherEduBackground = queryObj.execute(sql=sql).getResult()/>
					</cfif>
						
					<cfif rs_teacher.recordCount EQ 0>

							<!--- 记录不存在 需要追加 --->
						
							<!--- 计数器累加 --->
							<cfset dataAdded++ />
						
							<!--- 写入数据库 --->
						    <cfset sql = "INSERT INTO t_teacher (
													tch_id,
													tch_name,
													tch_sex,
													tch_idcard,
													tch_email,
													tch_create,
													tch_lastupdate,
													tch_type,
													tp_id,
													edu_id,
													institute_id
											) VALUES (
												:TeacherID,
												:TeacherName,
												:TeacherSex,
												:TeacherIDCard,
												:TeacherEmail,
												:ProfileCreate,
												:ProfileLastUpdate,
												:Type,
												:TechnicalPosition,
												:TeacherEducational,
												:Institute 	
											)"/>
                           	<cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name="TeacherID", value=queryTeacher.tid, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherName", value=queryTeacher.tchname, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherSex", value=queryTeacher.gender, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TeacherIDCard", value=queryTeacher.idcard, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherEmail", value=queryTeacher.tid&'@email.unknown', cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="ProfileCreate", value=currentTS, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="ProfileLastUpdate", value=currentTS, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Type", value=queryTeacher.tchtype, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TeacherEducational", value=queryTeacher.educode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TechnicalPosition", value=queryTeacher.tpcode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Institute", value=queryTeacher.depid, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.execute(sql=sql)/>
						<cfelse>
						
							<!--- 记录已存在 根据中心数据进行更新 --->
							
							<!--- 更新职称 --->
							
							<!--- 更新学历 --->
						
							<!--- 计数器累加 --->
							<cfset dataUpdated++ />
							<!--- 写入数据库 --->
							
                            <cfset sql = "UPDATE t_teacher a 
											SET a.tp_id = :TechnicalPosition ,
												a.edu_id = :TeacherEducational ,
												a.institute_id = :Institute 
											WHERE a.tch_id = :TeacherID "/>
                                            
                            <cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name="TeacherID", value=rs_teacher.tch_id, cfsqltype=
							"cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherEducational", value=queryTeacher.educode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TechnicalPosition", value=queryTeacher.tpcode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Institute", value=queryTeacher.depid, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.execute(sql=sql)/>
							
					</cfif>
				</cfloop>
			</cftransaction>

			<cfcatch type="any">
				<cfset hasError = true />
				<p class="warn">记录处理异常: <cfoutput>#cfcatch.Detail#</cfoutput></p>
				<p class="warn">回滚数据</p>
			</cfcatch>

		</cftry>
		
	</cfcase>
	<cfcase value="update">

		<p class="warn">警告: 如果现有数据某些字段发生变更后没有及时转发至数据中心, 这些变更将丢失</p>
		
		<cftry>
		
			<cftransaction>
				<cfloop query="queryTeacher">
					<!--- 循环中心数据库记录, 尝试追加新纪录 --->
				
                    <cfset sql = "SELECT a.tch_id 
									FROM t_teacher a 
									WHERE a.tch_id = :tchId "/>
                         
                    <cfset queryObj = new Query(datasource = application.dnsMaster)/>
					<cfset queryObj.addParam(name="tchId", value=queryTeacher.tid, cfsqltype="cf_sql_varchar")/>
                    
                    <cfset rs_teacher = queryObj.execute(sql=sql).getResult()/>
					
					<!--- 字典组件缓存 --->
					<cfif rs_teacherType.recordCount GT 0 and rs_teacherType.tch_type eq queryTeacher.tchtype>
						<cfelse>
						
                            <cfset sql = "SELECT a.tch_type 
											FROM t_teacher_type a 
											WHERE a.tch_type = :tchId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="tchId", value=queryTeacher.tchtype, cfsqltype="cf_sql_char")/>
                            
                            <cfset rs_teacherType = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					<cfif rs_teacherTechnicalPosition.recordCount GT 0 and teacherTechnicalPosition.getTechnicalPositionID() eq queryTeacher.tpcode>
						<cfelse>
							
                            <cfset sql = "SELECT a.tp_id  
											FROM t_technical_position a 
											WHERE a.tp_id = :tchId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="tchId", value=queryTeacher.tpcode, cfsqltype="cf_sql_char")/>
                            
                            <cfset rs_teacherTechnicalPosition = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					<cfif rs_teacherDepartment.recordCount GT 0 and rs_teacherDepartment.institute_id eq queryTeacher.depid>
						<cfelse>
						
                            <cfset sql = "SELECT a.institute_id 
											FROM t_institute a 
											WHERE a.institute_id = :insId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="insId", value=queryTeacher.depid, cfsqltype="cf_sql_varchar")/>
                            
                            <cfset rs_teacherDepartment = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					<cfif rs_teacherEduBackground.recordCount GT 0 and rs_teacherEduBackground.edu_id eq queryTeacher.educode>
						<cfelse>
						
                            <cfset sql = "SELECT a.edu_id 
											FROM t_teacher_educational a 
											WHERE a.edu_id = :tchId "/>
                    
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                            <cfset queryObj.addParam(name="tchId", value=queryTeacher.educode, cfsqltype="cf_sql_char")/>
                            
                            <cfset rs_teacherEduBackground = queryObj.execute(sql=sql).getResult()/>
					</cfif>
					
					<cfif rs_teacher.recordCount EQ 0>
							
							<!--- 记录不存在 需要追加 --->
							
							<!--- 计数器累加 --->
							<cfset dataAdded++ />
							
							<!--- 写入数据库 --->
						
                            <cfset sql = "INSERT INTO t_teacher (
													tch_id,
													tch_name,
													tch_sex,
													tch_idcard,
													tch_email,
													tch_create,
													tch_lastupdate,
													tch_type,
													tp_id,
													edu_id,
													institute_id
											) VALUES (
												:TeacherID,
												:TeacherName,
												:TeacherSex,
												:TeacherIDCard,
												:TeacherEmail,
												:ProfileCreate,
												:ProfileLastUpdate,
												:Type,
												:TechnicalPosition,
												:TeacherEducational,
												:Institute 	
											)"/>
                           	<cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name="TeacherID", value=queryTeacher.tid, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherName", value=queryTeacher.tchname, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherSex", value=queryTeacher.gender, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TeacherIDCard", value=queryTeacher.idcard, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherEmail", value=queryTeacher.tid&'@email.unknown', cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="ProfileCreate", value=currentTS, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="ProfileLastUpdate", value=currentTS, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Type", value=queryTeacher.tchtype, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TeacherEducational", value=queryTeacher.educode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TechnicalPosition", value=queryTeacher.tpcode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Institute", value=queryTeacher.depid, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.execute(sql=sql)/>
						
						<cfelse>
						
						
							<!--- 记录已存在 根据中心数据进行更新 --->
							
							<!--- 计数器累加 --->
							<cfset dataUpdated++ />
							<!--- 写入数据库 --->
						
                            <cfset sql = "UPDATE t_teacher a 
											SET a.tch_name = :TeacherName ,
												a.tch_sex = :TeacherSex ,
												a.tch_idcard = :TeacherIDCard ,
												a.tch_email = :TeacherEmail ,
												a.tch_create = :ProfileCreate ,
												a.tch_type = :Type , 
												a.tp_id = :TechnicalPosition ,
												a.edu_id = :TeacherEducational ,
												a.institute_id = :Institute 
											WHERE a.tch_id = :TeacherID "/> 
                            <cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name="TeacherID", value=rs_teacher.tch_id, cfsqltype=
"cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherName", value=queryTeacher.tchname, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherSex", value=queryTeacher.gender, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TeacherIDCard", value=queryTeacher.idcard, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="TeacherEmail", value=queryTeacher.tid&'@email.unknown', cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.addParam(name="ProfileCreate", value=currentTS, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Type", value=queryTeacher.tchtype, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TeacherEducational", value=queryTeacher.educode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="TechnicalPosition", value=queryTeacher.tpcode, cfsqltype="cf_sql_char")/>
                            <cfset queryObj.addParam(name="Institute", value=queryTeacher.depid, cfsqltype="cf_sql_varchar")/>
                            <cfset queryObj.execute(sql=sql)/>
					</cfif>
				</cfloop>
			</cftransaction>
		
			<cfcatch type="any">
				<cfset hasError = true />
				<p class="warn">记录处理异常: <cfoutput>#cfcatch.Message#</cfoutput></p>
				<p class="warn">回滚数据</p>
			</cfcatch>
		
		</cftry>
		
	</cfcase>
</cfswitch>

<cfif not isDefined("hasError")>
<p class="notice">追加数据 <cfoutput>#dataAdded#</cfoutput> 条, 更新数据 <cfoutput>#dataUpdated#</cfoutput> 条</p>
</cfif>